using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;

namespace Database
{
   public class LinhKien
    {
        private int malinhkien;

        public int Malinhkien
        {
            get { return malinhkien; }
            set { malinhkien = value; }
        }
        private string tenlinhkien;

        public string Tenlinhkien
        {
            get { return tenlinhkien; }
            set { tenlinhkien = value; }
        }        
        private double giaban;

        public double Giaban
        {
            get { return giaban; }
            set { giaban = value; }
        }       
        private string hinhanh;

        public string Hinhanh
        {
            get { return hinhanh; }
            set { hinhanh = value; }
        }
        private int soluong;

        public int Soluong
        {
            get { return soluong; }
            set { soluong = value; }
        }
        private int loailk;

        public int Loailk
        {
            get { return loailk; }
            set { loailk = value; }
        }
       public LinhKien()
       {
       }
       public LinhKien(int malinhkien, string tenlinhkien,float giaban,string hinhanh, int soluong, int loailk)
       {
           this.malinhkien = malinhkien;
           this.tenlinhkien = tenlinhkien;           
           this.giaban = giaban;           
           this.hinhanh = hinhanh;
           this.soluong = soluong;
           this.loailk = loailk;
       }
       public static DataSet DSLK()
       {
           DataSet ds = new DataSet();
           string query = "select lk.malinhkien,lk.tenlinhkien,lk.giaban,lk.soluong,llk.tenloailk from linhkien lk,loailinhkien llk where lk.loailk=llk.loailk";
           ConnectToDatabase db = new ConnectToDatabase();
           SqlDataAdapter sda = new SqlDataAdapter(query,db.GetConnect());
           sda.Fill(ds);
           return ds;
       }
       public DataSet DanhSachLinhKien()
       {
           DataSet ds = new DataSet();
           string query = "select top 9 * from linhkien order by soluong DESC";
           ConnectToDatabase db = new ConnectToDatabase();
           SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
           sda.Fill(ds);
           return ds;
       }
       public DataSet DSLinhKien()
       {
           DataSet ds = new DataSet();
           string query = "select * from linhkien";
           ConnectToDatabase db = new ConnectToDatabase();
           SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
           sda.Fill(ds);
           return ds;
       }
       public static DataSet DSLoaiLK()
       {
           DataSet ds = new DataSet();
           string query = "select distinct loailk from linhkien";
           ConnectToDatabase db = new ConnectToDatabase();
           SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
           sda.Fill(ds);
           return ds;
       }
       public bool ThemLK(string tenlinhkien,float giaban,string hinhanh, int soluong,int loailk)
       {
           bool kq = true;

           try
           {
               string query = "insert into LinhKien(tenlinhkien,giaban,hinhanh,soluong,loailk) values('{0}','{1}','{2}','{3}','{4}')";
               query = String.Format(query, tenlinhkien,giaban,hinhanh,soluong,loailk);
               ConnectToDatabase db = new ConnectToDatabase();
               SqlCommand cmd = new SqlCommand(query,db.GetConnect());
              return cmd.ExecuteNonQuery()>0;
           }
           catch
           {
               kq = false;
           }

           return kq;
       }
       public bool XoaLK(int malinhkien)
       {
           bool kq = true;

           try
           {
               string query = "Delete LinhKien where malinhkien='{0}'";
               query = String.Format(query,malinhkien);
               ConnectToDatabase db = new ConnectToDatabase();
               SqlCommand cmd = new SqlCommand(query, db.GetConnect());
               cmd.ExecuteNonQuery();
           }
           catch
           {
               kq = false;
           }

           return kq;
       }
       public bool CapNhatLK(int malinhkien,string tenlinhkien,float giaban,string hinhanh, int soluong, int loailk)
       {
           bool kq = true;

           try
           {
               string query = "update LinhKien set tenlinhkien='{0}',giaban='{1}',hinhanh='{2}',soluong='{3}',loailk='{4}' where malinhkien='{5}'";
               query = String.Format(query,tenlinhkien,giaban,hinhanh,soluong ,loailk,malinhkien);
               ConnectToDatabase db = new ConnectToDatabase();
               SqlCommand cmd = new SqlCommand(query,db.GetConnect());
              return cmd.ExecuteNonQuery()>0;
           }
           catch
           {
               kq = false;
           }

           return kq;
       }
       public LinhKien ThongTinLK(int malinhkien)
       {
           string query = "select * from LinhKien where malinhkien=" +malinhkien;
           ConnectToDatabase db = new ConnectToDatabase();
           SqlCommand sc = new SqlCommand(query, db.GetConnect());
           SqlDataReader reader = sc.ExecuteReader();
           LinhKien lk = new LinhKien();
           while (reader.Read())
           {
               lk.malinhkien = reader.GetInt32(0);
               lk.tenlinhkien = reader.GetString(1);               
               lk.giaban = reader.GetDouble(2);               
               lk.hinhanh = reader.GetString(3);
               lk.soluong = reader.GetInt32(4);
               lk.loailk = reader.GetInt32(5);               
           }
           return lk;
       }
       public DataSet DanhSachLKTheoLoaiLK(int loailk)
       {
           DataSet ds = new DataSet();
           try
           {
               string query = "select * from linhkien where loailk = {0}";
               query = string.Format(query, loailk);
               ConnectToDatabase db = new ConnectToDatabase();
               SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
               sda.Fill(ds);
           }
           catch
           {
               ds = null;
           }
           return ds;
       }
       public bool KiemTraLinhKien(int malinhkien)
       {
           bool kq = true;

           try
           {
               string query = "select * from linhkien where xacdinh = 1 and malinhkien = {0}";
               query = String.Format(query, malinhkien);
               ConnectToDatabase db = new ConnectToDatabase();
               SqlCommand cmd = new SqlCommand(query, db.GetConnect());
               int n = int.Parse(cmd.ExecuteScalar().ToString());
               if (n > 0)
               {
                   kq = true;
               }
               else
               {
                   kq = false;
               }

           }
           catch
           {
               kq = false;
           }

           return kq;
       }
    }
    
}
